﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Adex;
using Telerik.Web.UI;
using SubSonic.Sugar;
using System.Security;
using MediaMonitor.Common;
using Ionic.Zip;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
using System.Configuration;
using System.Web.UI.DataVisualization.Charting;
using OfficeOpenXml.Drawing.Chart;
using System.Collections;
using System.ComponentModel;

using office = Microsoft.Office.Interop.Excel;
using interop = System.Runtime.InteropServices;

namespace MediaMonitor.Users
{
    public partial class Report_competition : Adex.BasePage
    {
       
        int mediaTypeCode = 0;
        string mediaCodes = "";
        string sectorCode = "", subSectorCode = "", categoryCode = "";
        string brandCodes = "", brandCode = "";
        int mtc = 0;
        DataView dvInvestmentGraph = new DataView();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack) 
            {
               
            }
          
        }

        private void BindInvestmentChart()
        {
           
            dvInvestmentGraph = (DataView)objDSGraph.Select();
            Chart1.DataSource = dvInvestmentGraph;
           
            #region[Legend]
            //Chart1.Legends.Add("TelevisionInvestment").Title = "Investment";
            Chart1.Legends.Add("Legend1");
            Chart1.Legends[0].Enabled = true;
            Chart1.Legends[0].Docking = Docking.Top;
            Chart1.Legends[0].Alignment = System.Drawing.StringAlignment.Far;
            Chart1.Legends[0].BorderColor = System.Drawing.Color.Black;
            
            #endregion

            #region[Settings]
            // set Y axix range
            Chart1.ChartAreas["ChartArea1"].AxisY.Minimum = 0;

            Chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;
            Chart1.ChartAreas["ChartArea1"].AxisX.IsLabelAutoFit = true;
            Chart1.ChartAreas["ChartArea1"].AxisX.LabelAutoFitStyle = LabelAutoFitStyles.LabelsAngleStep45;

           Chart1.ChartAreas[0].AxisX.LabelStyle.Angle = -40; // Can vary from -90 to 90.

            Chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
            Chart1.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;
            #endregion

            #region[Chart]

            Chart1.Series["TELEVISION"].XValueMember = "BrandNames";
            Chart1.Series["TELEVISION"].YValueMembers = "TelevisionInvestment" ;

           // Chart1.ChartAreas[0].AxisY.LabelStyle.Format = "{#} $";

            Chart1.Series["RADIO"].XValueMember = "BrandNames"; 
            Chart1.Series["RADIO"].YValueMembers = "RadioInvestment";

            Chart1.Series["CABLE"].XValueMember = "BrandNames";
            Chart1.Series["CABLE"].YValueMembers = "CableInvestment";

            Chart1.Series["PRESS"].XValueMember = "BrandNames";
            Chart1.Series["PRESS"].YValueMembers = "PressInvestment";

            Chart1.Series["OUTDOOR"].XValueMember = "BrandNames";
            Chart1.Series["OUTDOOR"].YValueMembers = "OutdoorInvestment";

            Chart1.Series["INTERNET"].XValueMember = "BrandNames";
            Chart1.Series["INTERNET"].YValueMembers = "InternetInvestment";
            Chart1.DataBind();

            DateTime sDate = Convert.ToDateTime(Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/").ToString());
            DateTime eDate = Convert.ToDateTime(Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/").ToString());
            lblFromDate.Text = string.Format("{0:MMM dd , yyyy}", sDate);
            lblToDate.Text = string.Format("{0:MMM dd , yyyy}", eDate);
            lblCategory.Text = Request.QueryString["cc"].ToString();
            #endregion
        }

        private void PrepareGridColumns()
        {

           GridBoundColumn InvestmentColumn = radGrid.Columns.FindByUniqueName("RealCost") as GridBoundColumn;

            switch (AppUser.GetCountry())
            {
                case 58:
                    {
                        InvestmentColumn.DataFormatString = "<small>RD$ <b>{0:N2}</b><small>";
                        break;
                    }
                case 218:
                    {
                        InvestmentColumn.DataFormatString = "<small>TT$ <b>{0:N2}</b><small>";
                        break;
                    }
            }



        }

         protected void radGrid_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {

            string sDate = Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/");
            string eDate = Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/");

            bool searchFromAllMedia_TV = Utility.ToBool(Utility.GetRequestedVal("mcTV", "false"));
            bool searchFromAllMedia_Radio = Utility.ToBool(Utility.GetRequestedVal("mcRD", "false"));
            bool searchFromAllMedia_Cable = Utility.ToBool(Utility.GetRequestedVal("mcCB", "false"));
            bool searchFromAllMedia_Press = Utility.ToBool(Utility.GetRequestedVal("mcPR", "false"));
            bool searchFromAllMedia_OutDoor = Utility.ToBool(Utility.GetRequestedVal("mcOD", "false"));
            bool searchFromAllMedia_Internet = Utility.ToBool(Utility.GetRequestedVal("mcIN", "false"));

            bool searchTV = searchFromAllMedia_TV || Session["SearchMediaCodes_TV"].ToString() != "";
            bool searchRD = searchFromAllMedia_Radio || Session["SearchMediaCodes_RD"].ToString() != "";
            bool searchCB = searchFromAllMedia_Cable || Session["SearchMediaCodes_CB"].ToString() != "";
            bool searchPR = searchFromAllMedia_Press || Session["SearchMediaCodes_PR"].ToString() != "";
            bool searchOD = searchFromAllMedia_OutDoor || Session["SearchMediaCodes_OD"].ToString() != "";
            bool searchIN = searchFromAllMedia_Internet || Session["SearchMediaCodes_IN"].ToString() != "";

            mediaCodes = searchFromAllMedia_TV == false ? Session["SearchMediaCodes_TV"].ToString() : "";
            mediaCodes += searchFromAllMedia_Radio == false ? (Session["SearchMediaCodes_RD"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_RD"].ToString()) : "";
            mediaCodes += searchFromAllMedia_Cable == false ? (Session["SearchMediaCodes_CB"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_CB"].ToString()) : "";
            mediaCodes += searchFromAllMedia_Press == false ? (Session["SearchMediaCodes_PR"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_PR"].ToString()) : "";
            mediaCodes += searchFromAllMedia_OutDoor == false ? (Session["SearchMediaCodes_OD"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_OD"].ToString()) : "";
            mediaCodes += searchFromAllMedia_Internet == false ? (Session["SearchMediaCodes_IN"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_IN"].ToString()) : "";

            sectorCode = Utility.GetRequestedVal("s", "");
            subSectorCode = Utility.GetRequestedVal("ss", "");
            categoryCode = Utility.GetRequestedVal("cc", "");
            brandCode = Utility.GetRequestedVal("brnd", "");

            try
            {
                brandCodes = Session["SearchMediaCodes_Competition"].ToString();
            }
            catch
            {
               brandCodes = "";
               
            }

            objDS.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            objDS.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            objDS.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            objDS.SelectParameters["searchTV"].DefaultValue = searchTV.ToString();
            objDS.SelectParameters["searchRD"].DefaultValue = searchRD.ToString();
            objDS.SelectParameters["searchCB"].DefaultValue = searchCB.ToString();
            objDS.SelectParameters["searchPR"].DefaultValue = searchPR.ToString();
            objDS.SelectParameters["searchOD"].DefaultValue = searchOD.ToString();
            objDS.SelectParameters["mediaCodes"].DefaultValue = mediaCodes;
            objDS.SelectParameters["sectorName"].DefaultValue = sectorCode;
            objDS.SelectParameters["subSectorName"].DefaultValue = subSectorCode;
            objDS.SelectParameters["categoryName"].DefaultValue = categoryCode;
            objDS.SelectParameters["brandName"].DefaultValue = brandCode;
            objDS.SelectParameters["brandNames"].DefaultValue = brandCodes;
            objDS.SelectParameters["searchIN"].DefaultValue = searchIN.ToString();


            objDSGraph.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            objDSGraph.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            objDSGraph.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            objDSGraph.SelectParameters["searchTV"].DefaultValue = searchTV.ToString();
            objDSGraph.SelectParameters["searchRD"].DefaultValue = searchRD.ToString();
            objDSGraph.SelectParameters["searchCB"].DefaultValue = searchCB.ToString();
            objDSGraph.SelectParameters["searchPR"].DefaultValue = searchPR.ToString();
            objDSGraph.SelectParameters["searchOD"].DefaultValue = searchOD.ToString();
            objDSGraph.SelectParameters["mediaCodes"].DefaultValue = mediaCodes;
            objDSGraph.SelectParameters["sectorName"].DefaultValue = sectorCode;
            objDSGraph.SelectParameters["subSectorName"].DefaultValue = subSectorCode;
            objDSGraph.SelectParameters["categoryName"].DefaultValue = categoryCode;
            objDSGraph.SelectParameters["brandName"].DefaultValue = brandCode;
            objDSGraph.SelectParameters["brandNames"].DefaultValue = brandCodes;
            objDSGraph.SelectParameters["searchIN"].DefaultValue = searchIN.ToString();

            radGrid.DataSourceID = "objDS";

            BindInvestmentChart();

        }


        protected void radGridIn_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {

            string sDate = Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/");
            string eDate = Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/");

            bool searchFromAllMedia_TV = Utility.ToBool(Utility.GetRequestedVal("mcTV", "false"));
            bool searchFromAllMedia_Radio = Utility.ToBool(Utility.GetRequestedVal("mcRD", "false"));
            bool searchFromAllMedia_Cable = Utility.ToBool(Utility.GetRequestedVal("mcCB", "false"));
            bool searchFromAllMedia_Press = Utility.ToBool(Utility.GetRequestedVal("mcPR", "false"));
            bool searchFromAllMedia_OutDoor = Utility.ToBool(Utility.GetRequestedVal("mcOD", "false"));
            bool searchFromAllMedia_Internet = Utility.ToBool(Utility.GetRequestedVal("mcIN", "false"));

            bool searchTV = searchFromAllMedia_TV || Session["SearchMediaCodes_TV"].ToString() != "";
            bool searchRD = searchFromAllMedia_Radio || Session["SearchMediaCodes_RD"].ToString() != "";
            bool searchCB = searchFromAllMedia_Cable || Session["SearchMediaCodes_CB"].ToString() != "";
            bool searchPR = searchFromAllMedia_Press || Session["SearchMediaCodes_PR"].ToString() != "";
            bool searchOD = searchFromAllMedia_OutDoor || Session["SearchMediaCodes_OD"].ToString() != "";
            bool searchIN = searchFromAllMedia_Internet || Session["SearchMediaCodes_IN"].ToString() != "";

            mediaCodes = searchFromAllMedia_TV == false ? Session["SearchMediaCodes_TV"].ToString() : "";
            mediaCodes += searchFromAllMedia_Radio == false ? (Session["SearchMediaCodes_RD"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_RD"].ToString()) : "";
            mediaCodes += searchFromAllMedia_Cable == false ? (Session["SearchMediaCodes_CB"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_CB"].ToString()) : "";
            mediaCodes += searchFromAllMedia_Press == false ? (Session["SearchMediaCodes_PR"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_PR"].ToString()) : "";
            mediaCodes += searchFromAllMedia_OutDoor == false ? (Session["SearchMediaCodes_OD"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_OD"].ToString()) : "";
            mediaCodes += searchFromAllMedia_Internet == false ? (Session["SearchMediaCodes_IN"].ToString() == "" ? "" : "," + Session["SearchMediaCodes_IN"].ToString()) : "";

            sectorCode = Utility.GetRequestedVal("s", "");
            subSectorCode = Utility.GetRequestedVal("ss", "");
            categoryCode = Utility.GetRequestedVal("cc", "");
            brandCode = Utility.GetRequestedVal("brnd", "");

            try
            {
                brandCodes = Session["SearchMediaCodes_Competition"].ToString();
            }
            catch
            {
                brandCodes = "";

            }

            ObjDS1.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            ObjDS1.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            ObjDS1.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            ObjDS1.SelectParameters["searchTV"].DefaultValue = searchTV.ToString();
            ObjDS1.SelectParameters["searchRD"].DefaultValue = searchRD.ToString();
            ObjDS1.SelectParameters["searchCB"].DefaultValue = searchCB.ToString();
            ObjDS1.SelectParameters["searchPR"].DefaultValue = searchPR.ToString();
            ObjDS1.SelectParameters["searchOD"].DefaultValue = searchOD.ToString();
            ObjDS1.SelectParameters["mediaCodes"].DefaultValue = mediaCodes;
            ObjDS1.SelectParameters["sectorName"].DefaultValue = sectorCode;
            ObjDS1.SelectParameters["subSectorName"].DefaultValue = subSectorCode;
            ObjDS1.SelectParameters["categoryName"].DefaultValue = categoryCode;
            ObjDS1.SelectParameters["brandName"].DefaultValue = brandCode;
            ObjDS1.SelectParameters["brandNames"].DefaultValue = brandCodes;
            ObjDS1.SelectParameters["searchIN"].DefaultValue = searchIN.ToString();
            radGridIn.DataSourceID = "ObjDS1";


            ObjDsCountGraph.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            ObjDsCountGraph.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            ObjDsCountGraph.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            ObjDsCountGraph.SelectParameters["searchTV"].DefaultValue = searchTV.ToString();
            ObjDsCountGraph.SelectParameters["searchRD"].DefaultValue = searchRD.ToString();
            ObjDsCountGraph.SelectParameters["searchCB"].DefaultValue = searchCB.ToString();
            ObjDsCountGraph.SelectParameters["searchPR"].DefaultValue = searchPR.ToString();
            ObjDsCountGraph.SelectParameters["searchOD"].DefaultValue = searchOD.ToString();
            ObjDsCountGraph.SelectParameters["mediaCodes"].DefaultValue = mediaCodes;
            ObjDsCountGraph.SelectParameters["sectorName"].DefaultValue = sectorCode;
            ObjDsCountGraph.SelectParameters["subSectorName"].DefaultValue = subSectorCode;
            ObjDsCountGraph.SelectParameters["categoryName"].DefaultValue = categoryCode;
            ObjDsCountGraph.SelectParameters["brandName"].DefaultValue = brandCode;
            ObjDsCountGraph.SelectParameters["brandNames"].DefaultValue = brandCodes;
            ObjDsCountGraph.SelectParameters["searchIN"].DefaultValue = searchIN.ToString();
          
           
            
          
        }

        protected void ObjDS_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = radGrid.PageSize;
                e.InputParameters.Add("e", e);
            }
        }


        protected void ObjDS1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = radGridIn.PageSize;
                e.InputParameters.Add("e", e);
            }
        }


        protected void ObjDS_SelectingExport(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = 1;
                e.InputParameters.Add("e", e);
            }
        }

        protected void btnExport_Clicked(object sender, EventArgs e)
        {
            dvInvestmentGraph = (DataView)objDSGraph.Select();
            dvInvestmentGraph.Table.Columns.Remove("TotalRecords");
            DataTable dt = dvInvestmentGraph.ToTable();

            DataView dvCountGraph = new DataView();
            dvCountGraph = (DataView)ObjDsCountGraph.Select();
            dvCountGraph.Table.Columns.Remove("TotalRecords");
            dvCountGraph.Table.Columns.Remove("RowNum");
            DataTable dtCountGraph = dvCountGraph.ToTable();

            DumpExcel(dt, dtCountGraph); 
        }

        private DataTable CreateDatatableofUserSelection()
        {
            String SectorName = String.Empty;
            String SubSectorName = String.Empty;
            String CategoryName = String.Empty;


            if (Utility.GetRequestedVal("s", "") == "")
            {
                SectorName = "- ALL -";
            }
            else
            {
                SectorName = Utility.GetRequestedVal("s", "");
            }

            if (Utility.GetRequestedVal("ss", "") == "")
            {
                SubSectorName = "- ALL -";
            }
            else
            {
                SubSectorName = Utility.GetRequestedVal("ss", "");
            }

            if (Utility.GetRequestedVal("cc", "") == "")
            {
                CategoryName = "- ALL -";
            }
            else
            {
                CategoryName = Utility.GetRequestedVal("cc", "");
            }

            DataTable tableUserSelection = new DataTable();
            tableUserSelection.Columns.Add("Products", typeof(string));
            tableUserSelection.Columns.Add("User Selection", typeof(string));

            tableUserSelection.Rows.Add("Sector", SectorName);
            tableUserSelection.Rows.Add("Sub Sector", SubSectorName);
            tableUserSelection.Rows.Add("Category", CategoryName);

            return tableUserSelection;
        }
        private void DumpExcel(DataTable tbl,DataTable tblCount)
        {
            Int32 tblColumnCount = tbl.Columns.Count;
            int row = tbl.Rows.Count; 
            if (tblColumnCount > 0)
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    try
                    {
                        //Create the worksheet
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("MediaMonitor-CompetitionReport");

                        DataTable UserSelection = CreateDatatableofUserSelection();
                        ws.Cells["A1"].LoadFromDataTable(UserSelection, true);
                        ws.Cells["A1:B1"].Style.Font.Bold = true;

                        ws.Cells["A6"].LoadFromDataTable(tbl, true);
                        ws.DefaultColWidth = 20;
                       
                        //Applying Filter to the Headers. 
                        ws.Cells["A6:G6"].AutoFilter = true;

                        //Making Fonts Bold of  the Headers.
                        ws.Cells["A6:G6"].Style.Font.Bold = true;

                        string endRow = (row + 32).ToString();
                        string LastRow = (row + 32).ToString();
                        LastRow = "G" + LastRow;
                        endRow = "A" + endRow;
                        ws.Cells[endRow].LoadFromDataTable(tblCount, true);
                       // ws.Cells[endRow + ":" + LastRow].AutoFilter = true;
                        ws.Cells[endRow + ":" + LastRow].Style.Font.Bold = true;
                       
                        

                        var BarChart = ws.Drawings.AddChart("Competition Chart", eChartType.ColumnClustered) as ExcelBarChart;
                        
                        //Showing $ sign in Y-Axis.
                        BarChart.YAxis.Format = "###,### $";
                     

                        //Set top left corner to row 1 column 2
                        BarChart.SetPosition(row + 8, 2, 2, 2);
                        
                        //Setting Widh and height of graph.(First Parameter is for Width and second is for Height.)
                        BarChart.SetSize(800, 400);

                        //Adding different Series in the graph . Here in Add functuion , first parameter is for Y-Axis and second is for X-Axis. 
                        BarChart.Series.Add(ExcelRange.GetAddress(7, 2, row + 7, 2), ExcelRange.GetAddress(7, 1, row + 7, 1));
                        BarChart.Series.Add(ExcelRange.GetAddress(7, 3, row + 7, 3), ExcelRange.GetAddress(7, 1, row + 7, 1));
                        BarChart.Series.Add(ExcelRange.GetAddress(7, 4, row + 7, 4), ExcelRange.GetAddress(7, 1, row + 7, 1));
                        BarChart.Series.Add(ExcelRange.GetAddress(7, 5, row + 7, 5), ExcelRange.GetAddress(7, 1, row + 7, 1));
                        BarChart.Series.Add(ExcelRange.GetAddress(7, 6, row + 7, 6), ExcelRange.GetAddress(7, 1, row + 7, 1));
                        BarChart.Series.Add(ExcelRange.GetAddress(7, 7, row + 7, 7), ExcelRange.GetAddress(7, 1, row + 7, 1));
                      
                 
                        //Setting Title for the graph.
                        BarChart.Title.Text = "Investment Chart";
                       
                        //Setting Legends of the graph.
                        BarChart.Series[0].Header = "TELEVISION";
                        BarChart.Series[1].Header = "RADIO";
                        BarChart.Series[2].Header = "CABLE";
                        BarChart.Series[3].Header = "PRESS";
                        BarChart.Series[4].Header = "INTERNET";
                        BarChart.Series[5].Header = "OUTDOOR";
                        BarChart.Legend.Position = eLegendPosition.Top;
                        BarChart.Legend.Add();

                        //Giving Style to the  graph.(There is inbuild ENum eChartStyle).
                        BarChart.Style = eChartStyle.Style26;

                        
                    }
                    catch { }
                    Response.Clear();
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=MediaMonitor-CompetitionReport.xlsx");
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();
                }
            }
        }





    }
}